package com.xvaca.spcms.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.xvaca.spcms.bean.OrderBean;
import com.xvaca.spcms.bean.SettlementBean;
import com.xvaca.spcms.bean.SyncDataBean;
import com.xvaca.spcms.db.DBConnector;

public class OrderDAO {
	
	private static final Logger logger = Logger.getLogger(OrderDAO.class);
	
	private static final String INSERT_ORDER_SQL = "INSERT INTO orders " +
			"(channel_id, player, sender, receiver, linkid, sms_status, cmd, mo_at, created_at, product_code, status, updated_at, pipe_id, sync_sender, ip) " +
			"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	
	
	private static final String SELECT_SYNC_DATA = "SELECT id, cmd, mo_at, player, pipe_id, linkid, sms_status FROM orders WHERE status = '0' LIMIT ?";
	
	private static final String UPDATE_STATUS_SQL = "UPDATE orders SET status = ? WHERE id = ?";
	
	private static final String SELECT_ORDERS_BY_LINKID = "SELECT * FROM orders WHERE linkid = ? AND sms_status = ?";
	
	/**
	 * 插入数据
	 * @param order
	 */
	public int insertOrder(OrderBean order) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		int i = 0;
		
		try {
			conn = DBConnector.getConnection();
			pstmt = conn.prepareStatement(INSERT_ORDER_SQL);
			pstmt.setInt(1, order.getChannelId());
			pstmt.setString(2, order.getPayer());
			pstmt.setString(3, order.getSender());
			pstmt.setString(4, order.getReceiver());
			pstmt.setString(5, order.getLinkid());
			pstmt.setInt(6, order.getSmsStatus());
			pstmt.setString(7, order.getCmd().toUpperCase());
			pstmt.setString(8, order.getMoAt());
			pstmt.setString(9, order.getCreatedAt());
			pstmt.setString(10, order.getProductCode());
			pstmt.setInt(11, order.getStatus());
			pstmt.setString(12, order.getUpdatedAt());
			pstmt.setInt(13, order.getPipeId());
			pstmt.setString(14, order.getSyncSender());
			pstmt.setString(15, order.getIp());
			i = pstmt.executeUpdate();
			
		} catch (Exception e) {
			logger.error("插入同步数据异常", e);
		} finally {
			DBConnector.closeAll(conn, pstmt, null);
		}
		
		return i;
	}
	
	/**
	 * 获取按日统计数据
	 * @param startAt
	 * @param endAt
	 * @param groupBy
	 * @param channelId
	 * @param cmd
	 * @return
	 */
	public List<OrderBean> getOrderGroupCount(String startAt, String endAt, int channelId, int pipeId) {
		
		String sqlStr = "";
		if (channelId != 0) {
			sqlStr = " and channel_id = '" + channelId + "'";
		}
		
		if (pipeId != 0) {
			sqlStr = sqlStr + " and pipe_id = '" + pipeId + "'";
		}
		
		String sql = "select count(case when sms_status = 3 then sms_status end) as mo_count," +
				"			 count(case when sms_status = 4 then sms_status end) as order_count," +
				"			 count(case when status=1 and sms_status = 4 then status end) as sync_count, date(mo_at) as mo_at, DATE_FORMAT(mo_at,'%Y-%m-%d %H时') as mo_at_hour " +
				"		from orders where mo_at >= ? and mo_at <= ? " + sqlStr +
				"		group by date(mo_at),hour(mo_at) order by mo_at_hour asc";
		
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<OrderBean> list = new ArrayList<OrderBean>();
		
		try {
			conn = DBConnector.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, startAt + " 00:00:00");
			pstmt.setString(2, endAt + "23:59:59");
			
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				OrderBean order = new OrderBean();
				order.setMoAt(rs.getString("mo_at"));
				order.setMoCount(rs.getInt("mo_count"));
				order.setOrderCount(rs.getInt("order_count"));
				order.setSyncCount(rs.getInt("sync_count"));
				order.setMoAtHour(rs.getString("mo_at_hour"));
				
				list.add(order);
			}
			
		} catch (Exception e) {
			logger.error("查询分组数据异常", e);
		} finally {
			DBConnector.closeAll(conn, pstmt, rs);
		}
		
		return list;
		
	}
	
	public List<SettlementBean> getSettlementDateByPipes(String startAt, String endAt, int pipeId) {
		
		String sqlStr = "";
		if (pipeId != 0) {
			sqlStr = " and o.pipe_id = '" + pipeId + "' ";
		}
		
		String sql = "select o.cmd, " +
				"		date(mo_at) as mo_at, " +
				"		p.name as pipe_name," +
				"		p.price as pipe_price, " +
				"		c.name as channel_name, " +
				"		c.price as channel_price, " +
				"		count(case when sms_status = 3 then sms_status end) as mo_count, " +
				"		count(case when sms_status = 4 then sms_status end) as order_count," +
				"		count(case when o.status=1 then o.status end) as sync_count " +
				"	 from orders o " + 
				"		left join pipes p on o.pipe_id=p.id " +
				"		left join channels c on o.channel_id=c.id " +
				"	 where mo_at >= ? and mo_at <= ? " + sqlStr +
				"	 group by day(mo_at),o.pipe_id";
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<SettlementBean> list = new ArrayList<SettlementBean>();
		
		try {
			conn = DBConnector.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, startAt + " 00:00:00");
			pstmt.setString(2, endAt + "23:59:59");
			
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				SettlementBean s = new SettlementBean();
				s.setChannelName(rs.getString("channel_name"));
				s.setChannelPrice(rs.getInt("channel_price"));
				s.setCmd(rs.getString("cmd"));
				s.setEndAt(endAt);
				s.setMoAt(rs.getString("mo_at"));
				s.setOrderCount(rs.getInt("order_count"));
				s.setPipeName(rs.getString("pipe_name"));
				s.setPipePrice(rs.getInt("pipe_price"));
				s.setStartAt(startAt);
				s.setSyncCount(rs.getInt("sync_count"));
				
				list.add(s);
			}
			
		} catch (Exception e) {
			logger.error("查询结算数据异常", e);
		} finally {
			DBConnector.closeAll(conn, pstmt, rs);
		}
		
		return list;
		
	}
	
	/**
	 * 获取同步数据
	 * @param count
	 * @return
	 */
	public List<SyncDataBean> getSyncDate(int count) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<SyncDataBean> list = new ArrayList<SyncDataBean>();
		
		try {
			conn = DBConnector.getConnection();
			pstmt = conn.prepareStatement(SELECT_SYNC_DATA);
			pstmt.setInt(1, count);
			
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				SyncDataBean data = new SyncDataBean();
				data.setId(rs.getInt("id"));
				data.setCmd(rs.getString("cmd"));
				data.setMoAt(rs.getString("mo_at"));
				data.setPayer(rs.getString("player"));
				data.setPipeId(rs.getInt("pipe_id"));
				data.setLinkid(rs.getString("linkid"));
				data.setSmsStatus(rs.getInt("sms_status"));
				list.add(data);
			}
			
		} catch (Exception e) {
			logger.error("查询分组数据异常", e);
		} finally {
			DBConnector.closeAll(conn, pstmt, rs);
		}
		
		return list;
	}
	
	public void updateOrderStatus(int id, int status) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = DBConnector.getConnection();
			pstmt = conn.prepareStatement(UPDATE_STATUS_SQL);
			pstmt.setInt(1, status);
			pstmt.setInt(2, id);
			
			pstmt.executeUpdate();
			
		} catch (Exception e) {
			logger.error("更新同步数据异常", e);
		} finally {
			DBConnector.closeAll(conn, pstmt, null);
		}
	}
	
	public OrderBean getOrderByLinkid(String linkId) {
		
		OrderBean order = null;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			conn = DBConnector.getConnection();
			pstmt = conn.prepareStatement(SELECT_ORDERS_BY_LINKID);
			pstmt.setString(1, linkId);
			pstmt.setInt(2, 3); // 状态3
			
			rs = pstmt.executeQuery();
			
			if (rs.next()) {
				order = new OrderBean();
				order.setSender(rs.getString("sender"));
				order.setPipeId(rs.getInt("pipe_id"));
				order.setCmd(rs.getString("cmd"));
				order.setStatus(rs.getInt("status"));
			}
			
		} catch (Exception e) {
			logger.error("获取pipeid异常", e);
		} finally {
			DBConnector.closeAll(conn, pstmt, rs);
		}
		
		return order;
	}
	
	/**
	 * 检查制定的linkId是否已存在
	 * @param linkId
	 * @param smsStatus
	 * @return
	 */
	public boolean isLinkIdExist(String linkId, int smsStatus) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			conn = DBConnector.getConnection();
			pstmt = conn.prepareStatement(SELECT_ORDERS_BY_LINKID);
			pstmt.setString(1, linkId);
			pstmt.setInt(2, smsStatus);
			
			rs = pstmt.executeQuery();
			
			if (rs.next()) {
				return true;
			}
			
		} catch (Exception e) {
			logger.error("查询linkid是否存在异常", e);
		} finally {
			DBConnector.closeAll(conn, pstmt, rs);
		}
		
		return false;
	}
	
	
	
}
